﻿-- პრცედურა აყალიბებს speIns,speDel,speUpd და speGet  პროცედურების SQL SCRIPTტექსტსტებს მითითებული ცხრილისათვის
CREATE     PROCEDURE [dbo].[USPTOOLS_TBLSCRIPT_Generate_IUDGscripts]
(@TBL  SYSNAME)
 as

SET NOCOUNT ON 
-- DECLARE THE NAME OF THE TABLE
   
-- DECLARE @TBL SYSNAME
-- SET @TBL = 'BOOK'

-- DECLARE THE VARIABLES

   DECLARE @MAXITEMS AS INTEGER
   DECLARE @COUNTER AS INTEGER
   DECLARE @COLUMNNAME AS SYSNAME
   DECLARE @COLUMNTYPE AS SYSNAME
   DECLARE @ISINPRIKEY AS BIT
   DECLARE @ANDVAR AS INTEGER
   DECLARE @COUNTPRIKEYS AS INTEGER

-- CREATE AN ARRAY TO HOLD THE TABLE FIELDS

   DECLARE @MEMARRAY TABLE
   ( ID INT IDENTITY,
   COLUMN_NAME SYSNAME,
   COLUMN_TYPE SYSNAME,
   PRIMARYKEYFIELD BIT,
   MEMVAR_NAME SYSNAME,
   CCOLUMN_NAME SYSNAME)



-- FILL THE MEMORY TABLE WITH ALL THE FIELDS
   
INSERT @MEMARRAY (COLUMN_NAME,COLUMN_TYPE,PRIMARYKEYFIELD,MEMVAR_NAME,CCOLUMN_NAME) 
SELECT SC.NAME AS 'COLUMNNAME',
          CASE BT.NAME
               WHEN 'INT' THEN '[integer]'
               WHEN 'IMAGE'THEN '[image]'
               WHEN 'TEXT' THEN '[text]'
               WHEN 'VARCHAR' THEN '[varchar]('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
               WHEN 'NVARCHAR' THEN '[nvarchar]('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
               WHEN 'NCHAR' THEN '[nchar]('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
               WHEN 'CHAR' THEN '[char]('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
               WHEN 'VARBINARY' THEN '[varbinary]('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
               WHEN 'DECIMAL' THEN '[decimal]('+ RTRIM(LTRIM(STR(SC.XPREC)))+','+RTRIM(LTRIM(STR(SC.XSCALE)))++')'
               WHEN 'MONEY' THEN '[money]'
             ELSE TD.NAME
         END AS 'COLUMNTYPE',0,'',''
   FROM SYSCOLUMNS SC
   LEFT JOIN SYSTYPES TD ON TD.XUSERTYPE = SC.XUSERTYPE -- AND SYSTYPES.TYPE = SYSTYPES.XTYPE
   LEFT JOIN SYSTYPES BT ON BT.XUSERTYPE = TD.XTYPE
   WHERE ID = OBJECT_ID(@TBL) AND SC.iscomputed = 0
   ORDER BY SC.COLID

-- FIGURE OUT IF THERE IS A PRIMARY KEY. THERE SHOULD BE ONE FOR THIS TO WORK.

   DECLARE @PK_INDEX SYSNAME
   DECLARE @PK_INDID INTEGER
   DECLARE @PK_ID AS INTEGER
   SELECT @PK_INDEX=NAME FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ = OBJECT_ID(@TBL)
   SELECT @PK_INDID = INDID , @PK_ID=ID FROM SYSINDEXES WHERE NAME = @PK_INDEX

-- FIGURE OUT THE COLUMNS IN THE PRIMARY KEY

   DECLARE @PK_COLUMNS AS SYSNAME
   SET @PK_COLUMNS=''
   SET @COUNTER = 1
   WHILE INDEX_COL(@TBL,@PK_INDID,@COUNTER) IS NOT  NULL
   BEGIN
      SELECT @PK_COLUMNS=@PK_COLUMNS + '#'+ INDEX_COL(@TBL,@PK_INDID,@COUNTER) + '#,'
      SET @COUNTER = @COUNTER + 1
   END

-- SET THE PRIMARYKEYFIELD IN THE MEMARRAY

   SELECT @MAXITEMS = MAX(ID) FROM @MEMARRAY
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD FROM @MEMARRAY WHERE ID = @COUNTER
      IF CHARINDEX('#'+@COLUMNNAME+'#',@PK_COLUMNS) > 0 
      BEGIN
         UPDATE @MEMARRAY SET PRIMARYKEYFIELD = 1 WHERE ID = @COUNTER
      END
      SET @COUNTER=@COUNTER + 1
   END

-- PATCH THE FIELDS MEMVAR_NAME AND CCOLUMN_NAME

   DECLARE @MEMVARNAME SYSNAME
   DECLARE @CCOLUMNNAME SYSNAME
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      --REPLACE SPACES IN MEMVAR NAME
      SET @MEMVARNAME = '@'+REPLACE(@COLUMNNAME,' ','_')
      SET @CCOLUMNNAME = '['+ @COLUMNNAME+']'
      UPDATE @MEMARRAY  SET MEMVAR_NAME = @MEMVARNAME,CCOLUMN_NAME=@CCOLUMNNAME WHERE ID = @COUNTER
      SET @COUNTER=@COUNTER + 1
   END

-- GENERATE DELETE ALL OLD PROCEDURES
PRINT '-- ============================================='
PRINT '-- DROP ALL PROCEDURES FOR IUDG '
PRINT '-- OF TABLE '+ @TBL
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[speIns_'+@TBL+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[speIns_'+@TBL+']'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[speDel_'+@TBL+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[speDel_'+@TBL+']'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[speUpd_'+@TBL+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[speUpd_'+@TBL+']'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[speGet_'+@TBL+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[speGet_'+@TBL+']'
PRINT '-- ============================================='
PRINT 'GO'

PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO INSERT RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED BY : TIMS GROUP'
PRINT '-- GENERATED ON :'+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='

PRINT 'CREATE PROCEDURE speIns_'+@TBL
PRINT '('
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
--      IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
--      BEGIN
         IF @COUNTER < @MAXITEMS
            PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE+','
         ELSE
            PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE
--      END
      SET @COUNTER=@COUNTER + 1
   END
   
   PRINT ')' 
   PRINT 'AS'
   PRINT 'INSERT '+@TBL + ' ('

   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
--      IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
--      BEGIN
         IF @COUNTER < @MAXITEMS
            PRINT '   '+@CCOLUMNNAME+','
         ELSE
            PRINT '   '+@CCOLUMNNAME
--      END
      SET @COUNTER=@COUNTER + 1
   END
   PRINT ')' 
   PRINT 'VALUES ('
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
--      IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
--      BEGIN
         IF @COUNTER < @MAXITEMS
            PRINT '   '+@MEMVARNAME+','
         ELSE
            PRINT '   '+@MEMVARNAME
--      END
      SET @COUNTER=@COUNTER + 1
   END
   PRINT ')' 
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO UPDATE RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED BY : TIMS GROUP'
PRINT '-- GENERATED ON :'+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE speUpd_'+@TBL
PRINT '('
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @COUNTER < @MAXITEMS
         PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE+','
      ELSE
         PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE
      SET @COUNTER=@COUNTER + 1
   END
   
   PRINT ')' 
   PRINT 'AS'
   PRINT 'UPDATE '+@TBL + ' SET '
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
      BEGIN
         IF @COUNTER < @MAXITEMS
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME + ','
         ELSE
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME
      END
      SET @COUNTER=@COUNTER + 1
   END
   PRINT 'WHERE'
   SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
   SET @COUNTER = 1
   SET @COUNTPRIKEYS = 0
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @ISINPRIKEY = 1  
      BEGIN
         SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
         IF @COUNTPRIKEYS < @ANDVAR
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
         ELSE
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME
      END
      SET @COUNTER=@COUNTER + 1
   END
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO DELETE RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED BY : TIMS GROUP'
PRINT '-- GENERATED ON :'+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE speDel_'+@TBL
PRINT '('
   SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
   SET @COUNTER = 1
   SET @COUNTPRIKEYS = 0
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @ISINPRIKEY = 1  
      BEGIN
         SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
         IF @COUNTPRIKEYS < @ANDVAR
            PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE+','
         ELSE
            PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE
      END
      SET @COUNTER=@COUNTER + 1
   END   
   PRINT ')' 
   PRINT 'AS'
   PRINT 'DELETE FROM '+@TBL 
   PRINT 'WHERE'
   SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
   SET @COUNTER = 1
   SET @COUNTPRIKEYS = 0
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @ISINPRIKEY = 1  
      BEGIN
         SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
         IF @COUNTPRIKEYS < @ANDVAR
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
         ELSE
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME
      END
      SET @COUNTER=@COUNTER + 1
   END
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO GET RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED BY : TIMS GROUP'
PRINT '-- GENERATED ON :'+CONVERT(VARCHAR(20),GETDATE())
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE speGet_'+@TBL
PRINT '('
   SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
   SET @COUNTER = 1
   SET @COUNTPRIKEYS = 0
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @ISINPRIKEY = 1  
      BEGIN
         SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
         IF @COUNTPRIKEYS < @ANDVAR
            PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE+','
         ELSE
            PRINT '   '+@MEMVARNAME+'   '+@COLUMNTYPE
      END
      SET @COUNTER=@COUNTER + 1
   END 
   PRINT ')' 
   PRINT 'AS'
   PRINT 'SELECT '
   SET @COUNTER = 1
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      BEGIN
         IF @COUNTER < @MAXITEMS
            PRINT '   '+@CCOLUMNNAME+','
         ELSE
            PRINT '   '+@CCOLUMNNAME
      END
      SET @COUNTER=@COUNTER + 1
   END
   PRINT 'FROM '+@TBL 
   PRINT 'WHERE'
   SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
   SET @COUNTER = 1
   SET @COUNTPRIKEYS = 0
   WHILE @COUNTER <= @MAXITEMS
   BEGIN 
      SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
      IF @ISINPRIKEY = 1  
      BEGIN
         SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
         IF @COUNTPRIKEYS < @ANDVAR
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
         ELSE
            PRINT '   '+@CCOLUMNNAME+' = '+@MEMVARNAME
      END
      SET @COUNTER=@COUNTER + 1
   END
PRINT 'GO'


